ORM Test

This notebook tests Collin's changes to the ORM package.

Dependencies

SQLAlchemy



In [1]:

    
#Change to base project directory
import os
os.chdir("..")



In [7]:

    
#load main ORM package
from ORM.Mappers import orm, Cancer, CancerSynonym, DataSource,Drug, DrugSynonym, CancerSynonymDatasourceLink, DrugSynonymDatasourceLink

Create cancer and synonym objects

See Cancer.py in ORM.Mappers for object definitions



In [8]:

    
s1 = CancerSynonym(synonym="skin cancer")
c1 = Cancer(name="melanoma")

Next, link the synonym to cancer



In [9]:

    
c1.synonyms.append(s1)

#Alternatively, could also link from synonym to cancer: s1.cancer = c1

Note that s1 now has its cancer field populated



In [10]:

    
#Cancer object
s1.cancer

#Can be used as any ordinary Python object
print "Cancer for synonym 1: %s" % s1.cancer.name









    



Cancer for synonym 1: melanoma

Create another synonym for melanoma



In [11]:

    
s2 = CancerSynonym(synonym="another synonym", cancer=c1)

Note that c1 now has an additional synonym



In [12]:

    
for synonym in c1.synonyms:
    print synonym.synonym









    



skin cancer
another synonym

Link the synonyms to datasources



In [13]:

    
ds1 = DataSource("test data source", "NCI", "", "test_filename", version=None)

for synonym in c1.synonyms:
    synonym.datasources.append(ds1)
    
s3 = CancerSynonym(synonym="yet another synonym", cancer=c1, data_sources=[ds1])

Now synonyms have a datasource.



In [14]:

    
for synonym in c1.synonyms:
    for ds in synonym.datasources:
        print "|".join([c1.name, synonym.synonym, ds.name])









    



melanoma|skin cancer|test data source
melanoma|another synonym|test data source
melanoma|yet another synonym|test data source

Create a drug, drug synonym, and datasource



In [15]:

    
ds1 = DataSource("test drug datasource", "NCI", "", "test_filename", version=None)
d1 = Drug("Vemurafenib")
dsyn1 = DrugSynonym("Vem", drug=d1, data_sources=[ds1])

for s in d1.synonyms:
    for ds in s.datasources:
        print "|".join([d1.name, s.synonym, ds.name])









    



Vemurafenib|Vem|test drug datasource

Automagically create Cancer and Cancer Synonym object-relations



In [16]:

    
orm.Base.metadata.create_all()









    



2014-08-28 12:15:26,878 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    



INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    



2014-08-28 12:15:26,879 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,880 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    



INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    



2014-08-28 12:15:26,881 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,882 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("cancer")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("cancer")






    



2014-08-28 12:15:26,883 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,884 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("version")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("version")






    



2014-08-28 12:15:26,884 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,885 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drug")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drug")






    



2014-08-28 12:15:26,885 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,886 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drugbank")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drugbank")






    



2014-08-28 12:15:26,886 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,887 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drugsynonym")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drugsynonym")






    



2014-08-28 12:15:26,887 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,888 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("cancersynonym")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("cancersynonym")






    



2014-08-28 12:15:26,889 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,889 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("datasource")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("datasource")






    



2014-08-28 12:15:26,890 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,890 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("cancersynonymdatasourcelink")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("cancersynonymdatasourcelink")






    



2014-08-28 12:15:26,891 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,891 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("drugsynonymdatasourcelink")






    



INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("drugsynonymdatasourcelink")






    



2014-08-28 12:15:26,892 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,893 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE version (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	whatsnew VARCHAR, 
	date DATETIME, 
	PRIMARY KEY (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE version (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	whatsnew VARCHAR, 
	date DATETIME, 
	PRIMARY KEY (id)
)








    



2014-08-28 12:15:26,893 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,896 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT






    



2014-08-28 12:15:26,897 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE drugsynonym (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	synonym VARCHAR, 
	drug_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(drug_id) REFERENCES drug (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE drugsynonym (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	synonym VARCHAR, 
	drug_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(drug_id) REFERENCES drug (id)
)








    



2014-08-28 12:15:26,898 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,899 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT






    



2014-08-28 12:15:26,900 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE cancersynonym (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	synonym VARCHAR, 
	cancer_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(cancer_id) REFERENCES cancer (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE cancersynonym (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	synonym VARCHAR, 
	cancer_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(cancer_id) REFERENCES cancer (id)
)








    



2014-08-28 12:15:26,900 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,901 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT






    



2014-08-28 12:15:26,903 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE datasource (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	name VARCHAR, 
	authors VARCHAR, 
	additional_info VARCHAR, 
	filename VARCHAR, 
	version_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(version_id) REFERENCES version (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE datasource (
	created_at DATETIME, 
	id INTEGER NOT NULL, 
	name VARCHAR, 
	authors VARCHAR, 
	additional_info VARCHAR, 
	filename VARCHAR, 
	version_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(version_id) REFERENCES version (id)
)








    



2014-08-28 12:15:26,903 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,905 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT






    



2014-08-28 12:15:26,906 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE cancersynonymdatasourcelink (
	created_at DATETIME, 
	cancersynonym_id INTEGER NOT NULL, 
	datasource_id INTEGER NOT NULL, 
	PRIMARY KEY (cancersynonym_id, datasource_id), 
	FOREIGN KEY(cancersynonym_id) REFERENCES cancersynonym (id), 
	FOREIGN KEY(datasource_id) REFERENCES datasource (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE cancersynonymdatasourcelink (
	created_at DATETIME, 
	cancersynonym_id INTEGER NOT NULL, 
	datasource_id INTEGER NOT NULL, 
	PRIMARY KEY (cancersynonym_id, datasource_id), 
	FOREIGN KEY(cancersynonym_id) REFERENCES cancersynonym (id), 
	FOREIGN KEY(datasource_id) REFERENCES datasource (id)
)








    



2014-08-28 12:15:26,906 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,907 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT






    



2014-08-28 12:15:26,908 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE drugsynonymdatasourcelink (
	created_at DATETIME, 
	drugsynonym_id INTEGER NOT NULL, 
	datasource_id INTEGER NOT NULL, 
	PRIMARY KEY (drugsynonym_id, datasource_id), 
	FOREIGN KEY(drugsynonym_id) REFERENCES drugsynonym (id), 
	FOREIGN KEY(datasource_id) REFERENCES datasource (id)
)








    



INFO:sqlalchemy.engine.base.Engine:
CREATE TABLE drugsynonymdatasourcelink (
	created_at DATETIME, 
	drugsynonym_id INTEGER NOT NULL, 
	datasource_id INTEGER NOT NULL, 
	PRIMARY KEY (drugsynonym_id, datasource_id), 
	FOREIGN KEY(drugsynonym_id) REFERENCES drugsynonym (id), 
	FOREIGN KEY(datasource_id) REFERENCES datasource (id)
)








    



2014-08-28 12:15:26,909 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



2014-08-28 12:15:26,910 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT

Create a SQLAlchemy Session (a connection to a database)

We interact with the database through a session. We can add rows, query, and perform all the normal database operations through a session.



In [17]:

    
session = orm.loadSession()

Adding instances (rows) to a table requires two steps: adding to a session and committing



In [18]:

    
session.add(c1)
session.commit()









    



2014-08-28 12:15:36,238 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)






    



INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)






    



2014-08-28 12:15:36,239 INFO sqlalchemy.engine.base.Engine INSERT INTO cancer (created_at, name) VALUES (CURRENT_TIMESTAMP, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancer (created_at, name) VALUES (CURRENT_TIMESTAMP, ?)






    



2014-08-28 12:15:36,240 INFO sqlalchemy.engine.base.Engine ('melanoma',)






    



INFO:sqlalchemy.engine.base.Engine:('melanoma',)






    



2014-08-28 12:15:36,243 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-28 12:15:36,244 INFO sqlalchemy.engine.base.Engine ('skin cancer', 2)






    



INFO:sqlalchemy.engine.base.Engine:('skin cancer', 2)






    



2014-08-28 12:15:36,245 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-28 12:15:36,246 INFO sqlalchemy.engine.base.Engine ('another synonym', 2)






    



INFO:sqlalchemy.engine.base.Engine:('another synonym', 2)






    



2014-08-28 12:15:36,246 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonym (created_at, synonym, cancer_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-28 12:15:36,247 INFO sqlalchemy.engine.base.Engine ('yet another synonym', 2)






    



INFO:sqlalchemy.engine.base.Engine:('yet another synonym', 2)






    



2014-08-28 12:15:36,248 INFO sqlalchemy.engine.base.Engine INSERT INTO datasource (created_at, name, authors, additional_info, filename, version_id) VALUES (CURRENT_TIMESTAMP, ?, ?, ?, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO datasource (created_at, name, authors, additional_info, filename, version_id) VALUES (CURRENT_TIMESTAMP, ?, ?, ?, ?, ?)






    



2014-08-28 12:15:36,249 INFO sqlalchemy.engine.base.Engine ('test data source', 'NCI', '', 'test_filename', None)






    



INFO:sqlalchemy.engine.base.Engine:('test data source', 'NCI', '', 'test_filename', None)






    



2014-08-28 12:15:36,250 INFO sqlalchemy.engine.base.Engine INSERT INTO cancersynonymdatasourcelink (created_at, cancersynonym_id, datasource_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



INFO:sqlalchemy.engine.base.Engine:INSERT INTO cancersynonymdatasourcelink (created_at, cancersynonym_id, datasource_id) VALUES (CURRENT_TIMESTAMP, ?, ?)






    



2014-08-28 12:15:36,250 INFO sqlalchemy.engine.base.Engine ((2, 1), (3, 1), (1, 1))






    



INFO:sqlalchemy.engine.base.Engine:((2, 1), (3, 1), (1, 1))






    



2014-08-28 12:15:36,251 INFO sqlalchemy.engine.base.Engine COMMIT






    



INFO:sqlalchemy.engine.base.Engine:COMMIT

Note that we only need to add and commit the Cancer object c1 -- the synonym objects it is linked to will automatically be stored in the Cancer Synonyms table

Query the cancer table



In [19]:

    
cancers = session.query(Cancer).all()









    



2014-08-28 12:15:46,324 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)






    



INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)






    



2014-08-28 12:15:46,325 INFO sqlalchemy.engine.base.Engine SELECT cancer.created_at AS cancer_created_at, cancer.id AS cancer_id, cancer.name AS cancer_name 
FROM cancer






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancer.created_at AS cancer_created_at, cancer.id AS cancer_id, cancer.name AS cancer_name 
FROM cancer






    



2014-08-28 12:15:46,326 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()



In [20]:

    
for cancer in cancers:
    print "Cancer: %s" % cancer.name
    print "Synonyms: %s" % ','.join([syn.synonym for syn in cancer.synonyms])









    



Cancer: melanoma
2014-08-28 12:16:52,031 INFO sqlalchemy.engine.base.Engine SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym 
WHERE ? = cancersynonym.cancer_id






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym 
WHERE ? = cancersynonym.cancer_id






    



2014-08-28 12:16:52,032 INFO sqlalchemy.engine.base.Engine (1,)






    



INFO:sqlalchemy.engine.base.Engine:(1,)






    



Synonyms: 
Cancer: melanoma
2014-08-28 12:16:52,034 INFO sqlalchemy.engine.base.Engine SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym 
WHERE ? = cancersynonym.cancer_id






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym 
WHERE ? = cancersynonym.cancer_id






    



2014-08-28 12:16:52,034 INFO sqlalchemy.engine.base.Engine (2,)






    



INFO:sqlalchemy.engine.base.Engine:(2,)






    



Synonyms: skin cancer,another synonym,yet another synonym

Query the CancerSynonyms table



In [22]:

    
synonyms = session.query(CancerSynonym).all()

for synonym in synonyms:
    print synonym.cancer.name, synonym.synonym









    



2014-08-28 12:17:35,190 INFO sqlalchemy.engine.base.Engine SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym






    



INFO:sqlalchemy.engine.base.Engine:SELECT cancersynonym.created_at AS cancersynonym_created_at, cancersynonym.id AS cancersynonym_id, cancersynonym.synonym AS cancersynonym_synonym, cancersynonym.cancer_id AS cancersynonym_cancer_id 
FROM cancersynonym






    



2014-08-28 12:17:35,191 INFO sqlalchemy.engine.base.Engine ()






    



INFO:sqlalchemy.engine.base.Engine:()






    



melanoma skin cancer
melanoma another synonym
melanoma yet another synonym



In [ ]: